

/**
	AUTHOR		: GANDHI
	DATE		: 25 JULY, 2005
	DESCRIPTION	: THIS PROC IS USED TO GET SALES FORECAST DETAILS BY MONTH BASED ON SEARCH CRITERIA. 
	MODIFIED BY     : SRIKANTH ON 1st AUG 2005
			  GANDHI & SRIKANTH ON 2ND AUG 2005 	

	EXEC SP_NEW_SALES_FORECAST_REPORT_BY_MONTH  '0','0','0',0,2005,'0','0',0,'byMonth',0,11,42

*/


ALTER       PROC SP_NEW_SALES_FORECAST_REPORT_BY_MONTH(
 
 
 @ODM_INPUT VARCHAR(100), 
 @PRODUCT_INPUT VARCHAR(100),
 @SPERSON_INPUT VARCHAR(100),
 @QUARTER INT,		--1 OR 2 OR 3 OR 4 	
 @YEAR INT, 
 @TYPE VARCHAR(20),   --0
 @defaultPrompt varchar(20),
 @CONF_LEVEL FLOAT,
 @PERIOD VARCHAR(20),	--byMonth
 @FROM_MONTH INT,	--starts from Zero(0 to 11)
 @TO_MONTH INT, 	--starts from Zero(0 to 11)
 @CUSTOMER_INPUT VARCHAR(100) )
 
  
 AS
 BEGIN
	
	
	DECLARE @INITIAL_RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT,USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50), SPERSON_NAME VARCHAR(100), QUARTER_START INT, QUARTER_END INT,QUANTITY0 INT, QUANTITY1 INT,QUANTITY2 INT,QUANTITY3 INT,QUANTITY4 INT,QUANTITY5 INT,QUANTITY6 INT,QUANTITY7 INT,QUANTITY8 INT,QUANTITY9 INT,QUANTITY10 INT,QUANTITY11 INT,
					PRICE0 FLOAT , PRICE1 FLOAT ,PRICE2 FLOAT ,PRICE3 FLOAT ,PRICE4 FLOAT ,PRICE5 FLOAT ,PRICE6 FLOAT ,PRICE7 FLOAT ,PRICE8 FLOAT ,PRICE9 FLOAT ,PRICE10 FLOAT ,PRICE11 FLOAT ,CONFIDENCE_LEVEL0 FLOAT,  CONFIDENCE_LEVEL1 FLOAT,CONFIDENCE_LEVEL2 FLOAT,CONFIDENCE_LEVEL3 FLOAT,  CONFIDENCE_LEVEL4 FLOAT,CONFIDENCE_LEVEL5 FLOAT,CONFIDENCE_LEVEL6 FLOAT,  CONFIDENCE_LEVEL7 FLOAT,CONFIDENCE_LEVEL8 FLOAT,CONFIDENCE_LEVEL9 FLOAT,  CONFIDENCE_LEVEL10 FLOAT,CONFIDENCE_LEVEL11 FLOAT)
	DECLARE @RESULTS_TABLE TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT,USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50), SPERSON_NAME VARCHAR(100))
	DECLARE @RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, QUANTITY_value INT,QUANTITY_NAME varchar(20), PRICE_value FLOAT ,PRICE_name varchar(20) , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL_VALUE FLOAT,CONFIDENCE_LEVEL_name varchar(30), CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50), SPERSON_NAME VARCHAR(100), QUARTER_START INT, QUARTER_END INT)
 	DECLARE @SPERSON_RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), QUARTER INT, YEAR INT,USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50), SPERSON_NAME VARCHAR(100),QUANTITY0 INT, QUANTITY1 INT,QUANTITY2 INT,QUANTITY3 INT,QUANTITY4 INT,QUANTITY5 INT,QUANTITY6 INT,QUANTITY7 INT,QUANTITY8 INT,QUANTITY9 INT,QUANTITY10 INT,QUANTITY11 INT,
					PRICE0 FLOAT , PRICE1 FLOAT ,PRICE2 FLOAT ,PRICE3 FLOAT ,PRICE4 FLOAT ,PRICE5 FLOAT ,PRICE6 FLOAT ,PRICE7 FLOAT ,PRICE8 FLOAT ,PRICE9 FLOAT ,PRICE10 FLOAT ,PRICE11 FLOAT ,CONFIDENCE_LEVEL0 FLOAT,  CONFIDENCE_LEVEL1 FLOAT,CONFIDENCE_LEVEL2 FLOAT,CONFIDENCE_LEVEL3 FLOAT,  CONFIDENCE_LEVEL4 FLOAT,CONFIDENCE_LEVEL5 FLOAT,CONFIDENCE_LEVEL6 FLOAT,  CONFIDENCE_LEVEL7 FLOAT,CONFIDENCE_LEVEL8 FLOAT,CONFIDENCE_LEVEL9 FLOAT,  CONFIDENCE_LEVEL10 FLOAT,CONFIDENCE_LEVEL11 FLOAT)

	DECLARE @START_MONTH INT 
	DECLARE @END_MONTH INT
		
	IF @PERIOD IS NOT NULL AND LTRIM(RTRIM(@PERIOD)) = 'byMonth'
	BEGIN
		SET @START_MONTH   = 	@FROM_MONTH
		SET @END_MONTH 	   = 	@TO_MONTH
	END


	DECLARE @SF_SPID INT
	DECLARE @SF_SPERSON_NAME VARCHAR(100)
	DECLARE @SF_CUSTOMER VARCHAR(100)
 	DECLARE @SF_PRODUCT VARCHAR(50)
 	DECLARE @SF_PROJECT VARCHAR(50)
 	DECLARE @SF_ODM VARCHAR(100)
	DECLARE @SF_CUSTOMER_NAME VARCHAR(100)
 	DECLARE @SF_PRODUCT_NAME VARCHAR(50)
 	DECLARE @SF_PROJECT_NAME VARCHAR(50)
 	DECLARE @SF_ODM_NAME VARCHAR(100)
 	DECLARE @SF_MONTH INT
	DECLARE @SF_QUARTER INT
 	DECLARE @SF_YEAR INT
 	DECLARE @SF_QUANTITY INT
	DECLARE @SF_PRICE FLOAT
 	DECLARE @SF_USERLIST1 VARCHAR(100)
	DECLARE @SF_USERDATE1 VARCHAR(100)
	DECLARE @SF_USERTXT1 VARCHAR(100)
	DECLARE @SF_USERTXT2 VARCHAR(100)
	DECLARE @SF_CONFIDENCE_LEVEL FLOAT
	DECLARE @MKTG_PART_NUMBER VARCHAR(100)
	DECLARE @SPERSON_SPID INT
	DECLARE @LEVEL_ORDER INT
	DECLARE @FC_MONTH VARCHAR(50)	
	DECLARE @FC_QUARTER VARCHAR(50)
	DECLARE @SF_FORECAST_TYPE VARCHAR(50)
	DECLARE @SF_BUILDNO VARCHAR(50)
	DECLARE @QUARTER_START INT
	DECLARE @QUARTER_END INT
	
	DECLARE @FORECASTDETAILS_CURSOR CURSOR
	DECLARE @QTY_CURSOR CURSOR

	DECLARE @BUILDNO VARCHAR(100)
	DECLARE @SPERSON_ID INT
	DECLARE @CUSTOMER VARCHAR(100)
	DECLARE @PROJECT VARCHAR(100)
	DECLARE @PRODUCT VARCHAR(100)
	DECLARE @ODM VARCHAR(100)
	DECLARE @MONTH INT
	DECLARE @USERLIST1  VARCHAR(100)
	DECLARE @USERDATE1  VARCHAR(100)
	DECLARE @USERTXT1  VARCHAR(100)
	DECLARE @USERTXT2  VARCHAR(100)
	DECLARE @CUSTID  VARCHAR(100)
	DECLARE @PROJID  VARCHAR(100)
	DECLARE @PRODID VARCHAR(100)
	DECLARE @ODMID  VARCHAR(100)
	DECLARE @FORECAST_TYPE  VARCHAR(100)
	DECLARE @SPERSON_NAME VARCHAR(100)
	DECLARE @QUANTITY_VALUE INT
	DECLARE @QUANTITY_NAME VARCHAR(100)
	DECLARE @PRICE_VALUE FLOAT
	DECLARE @PRICE_NAME VARCHAR(100)
	DECLARE @CONFIDENCE_LEVEL_VALUE FLOAT
	DECLARE @CONFIDENCE_LEVEL_NAME  VARCHAR(100)

		
 	--DECLARE @CUSTOMER_INPUT VARCHAR(100)
	DECLARE @PROJECT_INPUT VARCHAR(100)
	--SET @CUSTOMER_INPUT = NULL
	SET @PROJECT_INPUT = NULL
	
	IF @CUSTOMER_INPUT IS NULL  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = ''  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = '-1'  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = '-2' 
	BEGIN	
		SET @CUSTOMER_INPUT = '0'
	END
	IF @PROJECT_INPUT IS NULL  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = ''  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = '-1'  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = '-2'
	BEGIN	
		SET @PROJECT_INPUT = '0'
	END
	IF @PRODUCT_INPUT IS NULL  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = ''  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = '-1'  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = '-2'
	BEGIN
		SET @PRODUCT_INPUT = '0'
	END
	IF @ODM_INPUT IS NULL  OR  LTRIM(RTRIM(@ODM_INPUT)) = ''  OR  LTRIM(RTRIM(@ODM_INPUT)) = '-1'  OR  LTRIM(RTRIM(@ODM_INPUT)) = '-2'
	BEGIN
		SET @ODM_INPUT = '0'
	END	
	IF @SPERSON_INPUT IS NULL  OR  LTRIM(RTRIM(@SPERSON_INPUT)) = ''  OR  LTRIM(RTRIM(@SPERSON_INPUT)) = '-1'  OR  LTRIM(RTRIM(@SPERSON_INPUT)) = '-2'
	BEGIN
		SET @SPERSON_INPUT = '0'
	END	

	DECLARE @REPORT_ON VARCHAR(10)
	SET @REPORT_ON = 'SALES'
	 	
 	DECLARE @SQLSTRING NVARCHAR(3000)
	DECLARE @PARAMDEFINITION NVARCHAR(500)

	IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
	BEGIN
		
		SET @SQLSTRING = N'DECLARE FORECASTCURSOR CURSOR FOR SELECT NULL, SPERSONID, CUSTID, PROJID, PRODID, ODMID, MONTH, QUARTER, YEAR, QTY, QTY*PRICE, USERLIST1, USERDATE1, USERTXT1, USERTXT2, CONFIDENCE_LEVEL, ''SALES'' FROM FCENTRYMASTER WHERE ROWID IS NOT NULL '
		SET @PARAMDEFINITION = N'@PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @PRM_SPERSON_INPUT VARCHAR(100), @START_MONTH INT, @END_MONTH INT, @YEAR INT, @PRM_CONF_LEVEL FLOAT, @PRM_CUSTOMER_INPUT VARCHAR(100)'
		
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PRODID = @PRM_PRODUCT_INPUT '
		END
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ODMID = @PRM_ODM_INPUT '
		END
		IF @SPERSON_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SPERSONID = @PRM_SPERSON_INPUT '
		END	
		IF @CONF_LEVEL <> -1.0
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CONFIDENCE_LEVEL >= @PRM_CONF_LEVEL '
		END
		IF @CUSTOMER_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CUSTID = @PRM_CUSTOMER_INPUT '
		END		
		
		SET @SQLSTRING = @SQLSTRING + ' AND ( MONTH >= @START_MONTH AND YEAR = @YEAR AND MONTH <= @END_MONTH ) '	

		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT,  @PRM_SPERSON_INPUT = @SPERSON_INPUT, @START_MONTH = @START_MONTH, @END_MONTH = @END_MONTH, @YEAR = @YEAR, @PRM_CONF_LEVEL = @CONF_LEVEL, @PRM_CUSTOMER_INPUT = @CUSTOMER_INPUT
	END

	
	SET @SQLSTRING = NULL
	SET @PARAMDEFINITION = NULL
	
	IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
	BEGIN
		
		SET @SQLSTRING = N'DECLARE SPERSON_CURSOR CURSOR FOR SELECT DISTINCT FCSPERSONREL.SPERSONID, LEVELORDER FROM FCSPERSONREL WHERE 
				   FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE ROWID IS NOT NULL'
		SET @PARAMDEFINITION = N'@PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @PRM_SPERSON_INPUT VARCHAR(100), @PRM_CUSTOMER_INPUT VARCHAR(100)'
		
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PRODID = @PRM_PRODUCT_INPUT '
		END
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ODMID = @PRM_ODM_INPUT '
		END
		IF @SPERSON_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SPERSONID = @PRM_SPERSON_INPUT '
		END
		IF @CUSTOMER_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CUSTID = @PRM_CUSTOMER_INPUT '
		END		
				
		SET @SQLSTRING = @SQLSTRING + ' ) ORDER BY LEVELORDER DESC, SPERSONID '	

		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT, @PRM_SPERSON_INPUT = @SPERSON_INPUT, @PRM_CUSTOMER_INPUT = @CUSTOMER_INPUT
	END
	
		DECLARE @I INT
		SET @I = 0
		DECLARE @QUANTITYSTRING NVARCHAR(100)
		DECLARE @PRICESTRING NVARCHAR(100)
		declare @CONFIDENCE_LEVEL_string NVARCHAR(100)

		DECLARE @PART_NUMBER_TYPE INT
		SELECT @PART_NUMBER_TYPE = PART_NUMBER_TYPE FROM SYSTEMPRFS

		OPEN FORECASTCURSOR 
 			FETCH NEXT FROM FORECASTCURSOR
 			INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE
 			WHILE @@FETCH_STATUS = 0
 			BEGIN
				SET @SF_CUSTOMER_NAME = NULL
				SET @SF_PROJECT_NAME = NULL
				SET @SF_PRODUCT_NAME = NULL
				SET @SF_ODM_NAME = NULL
				SET @SF_SPERSON_NAME = NULL
				SELECT @SF_CUSTOMER_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @SF_CUSTOMER
				SELECT @SF_PROJECT_NAME = PROJNAME FROM  PROJSETUP WHERE PROJID = @SF_PROJECT
				SELECT @SF_PRODUCT_NAME = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @SF_PRODUCT
				SELECT @SF_ODM_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @SF_ODM
				SELECT @SF_SPERSON_NAME = SALESPERSON_NAME FROM SALES_PERSONS WHERE SALESPERSON_ID = @SF_SPID

					SET @QUANTITYSTRING = N'@QUANTITY' + CONVERT(NVARCHAR , @SF_MONTH)
					SET @PRICESTRING = N'@PRICE' + CONVERT(NVARCHAR , @SF_MONTH)
					set @CONFIDENCE_LEVEL_string = N'@CONFIDENCE_LEVEL_string' + CONVERT(NVARCHAR , @SF_MONTH)

					INSERT INTO @RESULTS VALUES( @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER_NAME, @SF_PROJECT_NAME, @SF_PRODUCT_NAME, @SF_ODM_NAME, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY,@QUANTITYSTRING,@SF_PRICE ,@PRICESTRING , /*@SF_PRICE,*/ @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2,@SF_CONFIDENCE_LEVEL,@CONFIDENCE_LEVEL_string,/*@SF_CONFIDENCE_LEVEL,*/ @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_FORECAST_TYPE, @SF_SPERSON_NAME, @QUARTER_START, @QUARTER_END )
 					
				IF @PART_NUMBER_TYPE <> 1
				BEGIN	
					SELECT @MKTG_PART_NUMBER = PRODUCT.P_MARKETING_PART_NUMBER FROM PRODUCT,PROCESS_STEP  WHERE PRODUCT.PRODUCT_ID = @SF_PRODUCT AND PRODUCT.PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM PROCESS_STEP WHERE PS_TYPE_NAME LIKE 'FINISHED GOODS') AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID  
					UPDATE @RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE PRODUCT = @SF_PRODUCT_NAME
 				END
			
				SET @MKTG_PART_NUMBER = NULL					

 				FETCH NEXT FROM FORECASTCURSOR
 				INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE				
 			END	

 		CLOSE FORECASTCURSOR
 		DEALLOCATE FORECASTCURSOR
 		
		INSERT INTO @RESULTS_TABLE SELECT NULL,SPERSON_ID,CUSTOMER,PROJECT,PRODUCT,ODM,NULL,NULL,YEAR,USERLIST1 , USERDATE1 , USERTXT1 , USERTXT2 ,CUSTID , PROJID , PRODID, ODMID , FORECAST_TYPE , SPERSON_NAME FROM @RESULTS
					 GROUP BY SPERSON_ID,CUSTOMER,PROJECT,PRODUCT,ODM,YEAR,USERLIST1 , USERDATE1 , USERTXT1 , USERTXT2 ,CUSTID , PROJID , PRODID, ODMID , FORECAST_TYPE , SPERSON_NAME
	
		declare @Q_SPERSON_ID int
		declare @Q_CUSTID int
		declare @Q_PROJID int
		declare @Q_PRODID int
		declare @Q_ODMID int
		DECLARE @QUANTITY_Value0 VARCHAR(10)
		DECLARE @QUANTITY_Value1 VARCHAR(10)
		DECLARE @QUANTITY_Value2 VARCHAR(10)
		DECLARE @QUANTITY_Value3 VARCHAR(10)
		DECLARE @QUANTITY_Value4 VARCHAR(10)
		DECLARE @QUANTITY_Value5 VARCHAR(10)
		DECLARE @QUANTITY_Value6 VARCHAR(10)
		DECLARE @QUANTITY_Value7 VARCHAR(10)
		DECLARE @QUANTITY_Value8 VARCHAR(10)
		DECLARE @QUANTITY_Value9 VARCHAR(10)
		DECLARE @QUANTITY_Value10 VARCHAR(10)
		DECLARE @QUANTITY_Value11 VARCHAR(10)

		DECLARE @PRICE_Value0 VARCHAR(20)
		DECLARE @PRICE_Value1 VARCHAR(20)
		DECLARE @PRICE_Value2 VARCHAR(20)
		DECLARE @PRICE_Value3 VARCHAR(20)
		DECLARE @PRICE_Value4 VARCHAR(20)
		DECLARE @PRICE_Value5 VARCHAR(20)
		DECLARE @PRICE_Value6 VARCHAR(20)
		DECLARE @PRICE_Value7 VARCHAR(20)
		DECLARE @PRICE_Value8 VARCHAR(20)
		DECLARE @PRICE_Value9 VARCHAR(20)
		DECLARE @PRICE_Value10 VARCHAR(20)
		DECLARE @PRICE_Value11 VARCHAR(20)

		DECLARE @CONFIDENCE_LEVEL_Value0 VARCHAR(10)
		DECLARE @CONFIDENCE_LEVEL_Value1 VARCHAR(10)
		DECLARE @CONFIDENCE_LEVEL_Value2 VARCHAR(10)
		DECLARE @CONFIDENCE_LEVEL_Value3 VARCHAR(10)
		DECLARE @CONFIDENCE_LEVEL_Value4 VARCHAR(10)
		DECLARE @CONFIDENCE_LEVEL_Value5 VARCHAR(10)
		DECLARE @CONFIDENCE_LEVEL_Value6 VARCHAR(10)
		DECLARE @CONFIDENCE_LEVEL_Value7 VARCHAR(10)
		DECLARE @CONFIDENCE_LEVEL_Value8 VARCHAR(10)
		DECLARE @CONFIDENCE_LEVEL_Value9 VARCHAR(10)
		DECLARE @CONFIDENCE_LEVEL_Value10 VARCHAR(10)
		DECLARE @CONFIDENCE_LEVEL_Value11 VARCHAR(10)


		set @qty_cursor = cursor for select SPERSON_ID, PROJID , PRODID, ODMID  from @RESULTS_TABLE
		open @qty_cursor
		fetch next from @qty_cursor into @Q_SPERSON_ID, @Q_PROJID , @Q_PRODID, @Q_ODMID
		while @@fetch_status = 0
 		begin
			SET @FORECASTDETAILS_CURSOR = CURSOR FOR SELECT BUILDNO,SPERSON_ID,CUSTOMER,PROJECT,PRODUCT,ODM,MONTH,QUARTER,YEAR,USERLIST1 , USERDATE1 , USERTXT1 , USERTXT2 ,CUSTID , PROJID , PRODID, ODMID , FORECAST_TYPE , SPERSON_NAME,QUANTITY_value,QUANTITY_NAME,PRICE_value,PRICE_name,CONFIDENCE_LEVEL_VALUE,CONFIDENCE_LEVEL_name FROM @RESULTS 
				where SPERSON_ID = @Q_SPERSON_ID and PROJID = @Q_PROJID and PRODID = @Q_PRODID and ODMID = @Q_ODMID
			OPEN @FORECASTDETAILS_CURSOR
			FETCH NEXT FROM @FORECASTDETAILS_CURSOR
 	 		INTO @BUILDNO,@SPERSON_ID,@CUSTOMER,@PROJECT,@PRODUCT,@ODM,@MONTH,@QUARTER,@YEAR,@USERLIST1 , @USERDATE1 , @USERTXT1 ,@USERTXT2 ,@CUSTID , @PROJID , @PRODID, @ODMID , @FORECAST_TYPE , @SPERSON_NAME,@QUANTITY_value,@QUANTITY_NAME,@PRICE_value,@PRICE_name,@CONFIDENCE_LEVEL_VALUE,@CONFIDENCE_LEVEL_name 
			WHILE @@FETCH_STATUS = 0
 	 		BEGIN
	
					IF ( @CONFIDENCE_LEVEL_VALUE < 20 )
					BEGIN
						SET @CONFIDENCE_LEVEL_VALUE = 0.0;
					END
					ELSE IF ( @CONFIDENCE_LEVEL_VALUE < 45 )
					BEGIN
						SET @CONFIDENCE_LEVEL_VALUE = 25.0;
					END
					ELSE IF ( @CONFIDENCE_LEVEL_VALUE < 70 )
					BEGIN
						SET @CONFIDENCE_LEVEL_VALUE = 50.0;
					END
					ELSE IF ( @CONFIDENCE_LEVEL_VALUE < 85 )
					BEGIN
						SET @CONFIDENCE_LEVEL_VALUE = 75.0;
					END
					ELSE IF ( @CONFIDENCE_LEVEL_VALUE < 95 )
					BEGIN
						SET @CONFIDENCE_LEVEL_VALUE = 90.0;
					END
					ELSE
					BEGIN
						SET @CONFIDENCE_LEVEL_VALUE = 100.0;
					END


					IF (@QUANTITY_NAME like '@QUANTITY0' /*and @PRICE_name like '@PRICE0' and @CONFIDENCE_LEVEL_name like '@CONFIDENCE_LEVEL0'*/)
					BEGIN 
						SET @QUANTITY_Value0 = convert(varchar,@QUANTITY_VALUE)
						set @PRICE_Value0 = convert(varchar,@PRICE_Value)
						set @CONFIDENCE_LEVEL_Value0 =convert(varchar,@CONFIDENCE_LEVEL_Value)
					END
					else
					BEGIN 
						SET @QUANTITY_Value0 = NULL
						set @PRICE_Value0 = NULL
						set @CONFIDENCE_LEVEL_Value0 = NULL
					END
					
					IF (@QUANTITY_NAME like '@QUANTITY1' /*and @PRICE_name like '@PRICE1' and @CONFIDENCE_LEVEL_name like '@CONFIDENCE_LEVEL1'*/)
					BEGIN 
						SET @QUANTITY_Value1 = convert(varchar,@QUANTITY_VALUE)
						set @PRICE_Value1 = convert(varchar,@PRICE_Value)
						set @CONFIDENCE_LEVEL_Value1 =convert(varchar,@CONFIDENCE_LEVEL_Value)
					END
					ELSE
					BEGIN 
						SET @QUANTITY_Value1 = NULL
						set @PRICE_Value1 = NULL
						set @CONFIDENCE_LEVEL_Value1 = NULL
					END
					 IF (@QUANTITY_NAME = '@QUANTITY2' /*and @PRICE_name like '@PRICE2' and @CONFIDENCE_LEVEL_name like '@CONFIDENCE_LEVEL2'*/) 
					BEGIN 
						SET @QUANTITY_Value2 = convert(varchar,@QUANTITY_VALUE)
						set @PRICE_Value2 = convert(varchar,@PRICE_Value)
						set @CONFIDENCE_LEVEL_Value2 =convert(varchar,@CONFIDENCE_LEVEL_Value)
					END
					ELSE 
					BEGIN 
						SET @QUANTITY_Value2 = NULL
						set @PRICE_Value2 = NULL
						set @CONFIDENCE_LEVEL_Value2 = NULL
					END
					IF (@QUANTITY_NAME = '@QUANTITY3' /*and @PRICE_name like '@PRICE3' and @CONFIDENCE_LEVEL_name like '@CONFIDENCE_LEVEL3'*/)
					BEGIN 
						SET @QUANTITY_Value3 = convert(varchar,@QUANTITY_VALUE)
						set @PRICE_Value3 = convert(varchar,@PRICE_Value)
						set @CONFIDENCE_LEVEL_Value3 =convert(varchar,@CONFIDENCE_LEVEL_Value)
					END
					ELSE
					BEGIN 
						SET @QUANTITY_Value3 = NULL
						set @PRICE_Value3 = NULL
						set @CONFIDENCE_LEVEL_Value3 = NULL
					END 
					IF (@QUANTITY_NAME = '@QUANTITY4' /*and @PRICE_name like '@PRICE4' and @CONFIDENCE_LEVEL_name like '@CONFIDENCE_LEVEL4'*/)
					BEGIN 
						SET @QUANTITY_Value4 = convert(varchar,@QUANTITY_VALUE)
						set @PRICE_Value4 = convert(varchar,@PRICE_Value)
						set @CONFIDENCE_LEVEL_Value4 =convert(varchar,@CONFIDENCE_LEVEL_Value)
					END
					ELSE
					BEGIN 
						SET @QUANTITY_Value4 = NULL
						set @PRICE_Value4 = NULL
						set @CONFIDENCE_LEVEL_Value4 = NULL
					END
					 IF (@QUANTITY_NAME = '@QUANTITY5' /*and @PRICE_name like '@PRICE5' and @CONFIDENCE_LEVEL_name like '@CONFIDENCE_LEVEL5'*/)
					BEGIN 
						SET @QUANTITY_Value5 = convert(varchar,@QUANTITY_VALUE)
						set @PRICE_Value5 = convert(varchar,@PRICE_Value)
						set @CONFIDENCE_LEVEL_Value5 =convert(varchar,@CONFIDENCE_LEVEL_Value)
					END
					ELSE 
					BEGIN 
						SET @QUANTITY_Value5 = NULL
						set @PRICE_Value5 = NULL
						set @CONFIDENCE_LEVEL_Value5 = NULL
					END
					IF (@QUANTITY_NAME like '@QUANTITY6'/* and @PRICE_name like '@PRICE6' and @CONFIDENCE_LEVEL_name like '@CONFIDENCE_LEVEL6'*/)
					BEGIN 
						SET @QUANTITY_Value6 = convert(varchar,@QUANTITY_VALUE)
						set @PRICE_Value6 = convert(varchar,@PRICE_Value)
						set @CONFIDENCE_LEVEL_Value6 =convert(varchar,@CONFIDENCE_LEVEL_Value)
						
					END
					else
					BEGIN 
						SET @QUANTITY_Value6 = NULL
						set @PRICE_Value6 = NULL
						set @CONFIDENCE_LEVEL_Value6 = NULL
					END
					 IF (@QUANTITY_NAME = '@QUANTITY7' /*and @PRICE_name like '@PRICE7' and @CONFIDENCE_LEVEL_name like '@CONFIDENCE_LEVEL7'*/)
					BEGIN 
						SET @QUANTITY_Value7 = convert(varchar,@QUANTITY_VALUE)
						set @PRICE_Value7 = convert(varchar,@PRICE_Value)
						set @CONFIDENCE_LEVEL_Value7 =convert(varchar,@CONFIDENCE_LEVEL_Value)
					END
					ELSE
					BEGIN 
						SET @QUANTITY_Value7 = NULL
						set @PRICE_Value7 = NULL
						set @CONFIDENCE_LEVEL_Value7 = NULL
					END 
					IF (@QUANTITY_NAME = '@QUANTITY8' /*and @PRICE_name like '@PRICE8' and @CONFIDENCE_LEVEL_name like '@CONFIDENCE_LEVEL8'*/)
					BEGIN 
						SET @QUANTITY_Value8 = convert(varchar,@QUANTITY_VALUE)
						set @PRICE_Value8 = convert(varchar,@PRICE_Value)
						set @CONFIDENCE_LEVEL_Value8 =convert(varchar,@CONFIDENCE_LEVEL_Value)
					END
					ELSE 
					BEGIN 
						SET @QUANTITY_Value8 = NULL
						set @PRICE_Value8 = NULL
						set @CONFIDENCE_LEVEL_Value8 = NULL
					END
					IF (@QUANTITY_NAME = '@QUANTITY9' /*and @PRICE_name like '@PRICE9' and @CONFIDENCE_LEVEL_name like '@CONFIDENCE_LEVEL9'*/)
					BEGIN 
						SET @QUANTITY_Value9 = convert(varchar,@QUANTITY_VALUE)
						set @PRICE_Value9 = convert(varchar,@PRICE_Value)
						set @CONFIDENCE_LEVEL_Value9 =convert(varchar,@CONFIDENCE_LEVEL_Value)
					END
					ELSE
					BEGIN 
						SET @QUANTITY_Value9 = NULL
						set @PRICE_Value9 = NULL
						set @CONFIDENCE_LEVEL_Value9 = NULL
					END 
					IF (@QUANTITY_NAME = '@QUANTITY10'/* and @PRICE_name like '@PRICE10' and @CONFIDENCE_LEVEL_name like '@CONFIDENCE_LEVEL10'*/)
					BEGIN 
						SET @QUANTITY_Value10 = convert(varchar,@QUANTITY_VALUE)
						set @PRICE_Value10 = convert(varchar,@PRICE_Value)
						set @CONFIDENCE_LEVEL_Value10 =convert(varchar,@CONFIDENCE_LEVEL_Value)
					END
					ELSE 
					BEGIN 
						SET @QUANTITY_Value10 = NULL
						set @PRICE_Value10 = NULL
						set @CONFIDENCE_LEVEL_Value10 = NULL
					END
					IF (@QUANTITY_NAME = '@QUANTITY11'/* and @PRICE_name like '@PRICE11' and @CONFIDENCE_LEVEL_name like '@CONFIDENCE_LEVEL11'*/)
					BEGIN 
						SET @QUANTITY_Value11 = convert(varchar,@QUANTITY_VALUE)
						set @PRICE_Value11 = convert(varchar,@PRICE_Value)
						set @CONFIDENCE_LEVEL_Value11 =convert(varchar,@CONFIDENCE_LEVEL_Value)
					END
					else
					BEGIN 
						SET @QUANTITY_Value11 = NULL
						set @PRICE_Value11 = NULL
						set @CONFIDENCE_LEVEL_Value11 = NULL
					END
						
			
					INSERT INTO @INITIAL_RESULTS (BUILDNO,SPERSON_ID,CUSTOMER,PROJECT,PRODUCT,ODM,MONTH,QUARTER,YEAR,USERLIST1 , USERDATE1 , USERTXT1 , USERTXT2 ,CUSTID , PROJID , PRODID, ODMID , FORECAST_TYPE , SPERSON_NAME,QUARTER_START , QUARTER_END,QUANTITY0, QUANTITY1,QUANTITY2,QUANTITY3,QUANTITY4,QUANTITY5,QUANTITY6,QUANTITY7,QUANTITY8,QUANTITY9,QUANTITY10,QUANTITY11,PRICE0, PRICE1,PRICE2,PRICE3,PRICE4,PRICE5,PRICE6,PRICE7,PRICE8,PRICE9,PRICE10,PRICE11,CONFIDENCE_LEVEL0, CONFIDENCE_LEVEL1 ,CONFIDENCE_LEVEL2 ,CONFIDENCE_LEVEL3, CONFIDENCE_LEVEL4 ,CONFIDENCE_LEVEL5 ,CONFIDENCE_LEVEL6, CONFIDENCE_LEVEL7 ,CONFIDENCE_LEVEL8 ,CONFIDENCE_LEVEL9, CONFIDENCE_LEVEL10 ,CONFIDENCE_LEVEL11  ) 
									  VALUES (@BUILDNO,@SPERSON_ID,@CUSTOMER,@PROJECT,@PRODUCT,@ODM,@MONTH,@QUARTER,@YEAR,@USERLIST1 ,@USERDATE1 , @USERTXT1 , @USERTXT2 ,@CUSTID , @PROJID , @PRODID, @ODMID , @FORECAST_TYPE , @SPERSON_NAME,@QUARTER_START , @QUARTER_END,cast(@QUANTITY_Value0 as int), cast(@QUANTITY_Value1 as int),cast(@QUANTITY_Value2 as int),cast(@QUANTITY_Value3 as int),cast(@QUANTITY_Value4 as int),cast(@QUANTITY_Value5 as int),cast(@QUANTITY_Value6 as int),cast(@QUANTITY_Value7 as int),cast(@QUANTITY_Value8 as int),cast(@QUANTITY_Value9 as int),cast(@QUANTITY_Value10 as int),cast(@QUANTITY_Value11 as int), cast(@PRICE_Value0 as float), cast(@PRICE_Value1 as float),cast(@PRICE_Value2 as float),cast(@PRICE_Value3 as float),cast(@PRICE_Value4 as float),cast(@PRICE_Value5 as float),cast(@PRICE_Value6 as float),cast(@PRICE_Value7 as float),cast(@PRICE_Value8 as float),cast(@PRICE_Value9 as float),cast(@PRICE_Value10 as float),cast(@PRICE_Value11 as float),cast(@CONFIDENCE_LEVEL_Value0 as float), cast(@CONFIDENCE_LEVEL_Value1 as float),cast(@CONFIDENCE_LEVEL_Value2 as float) ,cast(@CONFIDENCE_LEVEL_Value3 as float), cast(@CONFIDENCE_LEVEL_Value4 as float),cast(@CONFIDENCE_LEVEL_Value5 as float),cast(@CONFIDENCE_LEVEL_Value6 as float), cast(@CONFIDENCE_LEVEL_Value7 as float),cast(@CONFIDENCE_LEVEL_Value8 as float),cast(@CONFIDENCE_LEVEL_Value9 as float), cast(@CONFIDENCE_LEVEL_Value10 as float),cast(@CONFIDENCE_LEVEL_Value11 as float) ) 
						SET @QUANTITY_Value0 = NULL
						SET @QUANTITY_Value1 = NULL
						SET @QUANTITY_Value2 = NULL
						SET @QUANTITY_Value3 = NULL
						SET @QUANTITY_Value4 = NULL
						SET @QUANTITY_Value5 = NULL
						SET @QUANTITY_Value6 = NULL
						SET @QUANTITY_Value7 = NULL
						SET @QUANTITY_Value8 = NULL
						SET @QUANTITY_Value9 = NULL
						SET @QUANTITY_Value10 = NULL
						SET @QUANTITY_Value11 = NULL
						
						SET @PRICE_Value0 = NULL
						SET @PRICE_Value1 = NULL
						SET @PRICE_Value2 = NULL
						SET @PRICE_Value3 = NULL
						SET @PRICE_Value4 = NULL
						SET @PRICE_Value5 = NULL
						SET @PRICE_Value6 = NULL
						SET @PRICE_Value7 = NULL
						SET @PRICE_Value8 = NULL
						SET @PRICE_Value9 = NULL
						SET @PRICE_Value10 = NULL
						SET @PRICE_Value11 = NULL
						
						SET @CONFIDENCE_LEVEL_Value0 = NULL
						SET @CONFIDENCE_LEVEL_Value1 = NULL
						SET @CONFIDENCE_LEVEL_Value2 = NULL
						SET @CONFIDENCE_LEVEL_Value3 = NULL
						SET @CONFIDENCE_LEVEL_Value4 = NULL
						SET @CONFIDENCE_LEVEL_Value5 = NULL
						SET @CONFIDENCE_LEVEL_Value6 = NULL
						SET @CONFIDENCE_LEVEL_Value7 = NULL
						SET @CONFIDENCE_LEVEL_Value8 = NULL
						SET @CONFIDENCE_LEVEL_Value9 = NULL
						SET @CONFIDENCE_LEVEL_Value10 = NULL
						SET @CONFIDENCE_LEVEL_Value11 = NULL

						SET @QUANTITY_VALUE = NULL
						SET @QUANTITY_NAME = NULL
						SET @PRICE_VALUE = NULL
						SET @PRICE_NAME = NULL
						SET @CONFIDENCE_LEVEL_VALUE = NULL
						SET @CONFIDENCE_LEVEL_NAME = NULL 

						SET @BUILDNO = NULL
						SET @SPERSON_ID = NULL
						SET @CUSTOMER = NULL
						SET @PROJECT = NULL
						SET @PRODUCT = NULL
						SET @ODM = NULL
						SET @MONTH = NULL
						SET @QUARTER = NULL
						SET @YEAR = NULL
						SET @USERLIST1  = NULL
						SET @USERDATE1  = NULL
						SET @USERTXT1  = NULL
						SET @USERTXT2  = NULL
						SET @CUSTID  = NULL
						SET @PROJID  = NULL
						SET @PRODID = NULL
						SET @ODMID  = NULL
						SET @FORECAST_TYPE  = NULL
						SET @SPERSON_NAME = NULL
						--SET @QUANTITY1 = NULL
						SET @QUANTITY_NAME = NULL
						--SET @PRICE1 = NULL
						SET @PRICE_name = NULL
						--SET @CONFIDENCE_LEVEL1 = NULL
						SET @CONFIDENCE_LEVEL_name  = NULL
	
					
				FETCH NEXT FROM @FORECASTDETAILS_CURSOR
 	 			INTO @BUILDNO,@SPERSON_ID,@CUSTOMER,@PROJECT,@PRODUCT,@ODM,@MONTH,@QUARTER,@YEAR,@USERLIST1 , @USERDATE1 , @USERTXT1 ,@USERTXT2 ,@CUSTID , @PROJID , @PRODID, @ODMID , @FORECAST_TYPE , @SPERSON_NAME,@QUANTITY_VALUE,@QUANTITY_NAME,@PRICE_value,@PRICE_name,@CONFIDENCE_LEVEL_value,@CONFIDENCE_LEVEL_name 
 	 			END
			CLOSE @FORECASTDETAILS_CURSOR
 			DEALLOCATE @FORECASTDETAILS_CURSOR 

		fetch next from @qty_cursor
		into @Q_SPERSON_ID, @Q_PROJID , @Q_PRODID, @Q_ODMID
		end
		close @qty_cursor
		deallocate @qty_cursor 


		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
 		BEGIN	
 			OPEN SPERSON_CURSOR 
 	 			FETCH NEXT FROM SPERSON_CURSOR
 	 			INTO @SPERSON_SPID, @LEVEL_ORDER
 	 			WHILE @@FETCH_STATUS = 0
 	 			BEGIN
 	
					INSERT INTO @SPERSON_RESULTS SELECT NULL , SPERSON_ID , CUSTOMER , PROJECT  , PRODUCT , ODM , NULL , YEAR ,USERLIST1 , USERDATE1 , USERTXT1 , USERTXT2 , CUSTID , PROJID , PRODID , ODMID , FORECAST_TYPE , SPERSON_NAME ,SUM(QUANTITY0) AS QUANTITY0, SUM(QUANTITY1) AS QUANTITY1 ,SUM(QUANTITY2) AS QUANTITY2,SUM(QUANTITY3) AS QUANTITY3,SUM(QUANTITY4) AS QUANTITY4,SUM(QUANTITY5) AS QUANTITY5,SUM(QUANTITY6) AS QUANTITY6 ,SUM(QUANTITY7) AS QUANTITY7,SUM(QUANTITY8) AS QUANTITY8,SUM(QUANTITY9) AS QUANTITY9,SUM(QUANTITY10) AS QUANTITY10,SUM(QUANTITY11)AS QUANTITY11, 
					sum(PRICE0)as PRICE0, sum(PRICE1)as PRICE1,sum(PRICE2)as PRICE2,sum(PRICE3)as PRICE3,sum(PRICE4)as PRICE4,sum(PRICE5)as PRICE5,sum(PRICE6)as PRICE6,sum(PRICE7)as PRICE7,sum(PRICE8)as PRICE8,sum(PRICE9)as PRICE9,sum(PRICE10)as PRICE10,sum(PRICE11)as PRICE11, 
					avg(CONFIDENCE_LEVEL0)as CONFIDENCE_LEVEL0, avg(CONFIDENCE_LEVEL1)as  CONFIDENCE_LEVEL1,avg(CONFIDENCE_LEVEL2)as  CONFIDENCE_LEVEL2,avg(CONFIDENCE_LEVEL3)as CONFIDENCE_LEVEL3, avg(CONFIDENCE_LEVEL4)as  CONFIDENCE_LEVEL4,avg(CONFIDENCE_LEVEL5)as  CONFIDENCE_LEVEL5,avg(CONFIDENCE_LEVEL6)as CONFIDENCE_LEVEL6, avg(CONFIDENCE_LEVEL7)as  CONFIDENCE_LEVEL7,avg(CONFIDENCE_LEVEL8)as CONFIDENCE_LEVEL8 ,avg(CONFIDENCE_LEVEL9)as CONFIDENCE_LEVEL9, avg(CONFIDENCE_LEVEL10)as CONFIDENCE_LEVEL10 ,avg(CONFIDENCE_LEVEL11)as CONFIDENCE_LEVEL11 FROM @INITIAL_RESULTS  WHERE SPERSON_ID = @SPERSON_SPID
					GROUP BY SPERSON_ID , CUSTOMER , PROJECT  , PRODUCT , ODM , YEAR ,USERLIST1 , USERDATE1 , USERTXT1 , USERTXT2 , CUSTID , PROJID , PRODID , ODMID , FORECAST_TYPE , SPERSON_NAME					
 					 										
 	 				FETCH NEXT FROM SPERSON_CURSOR
 	 				INTO @SPERSON_SPID, @LEVEL_ORDER
 	 			END	
 
 	 		CLOSE SPERSON_CURSOR
 	 		DEALLOCATE SPERSON_CURSOR 
 			
 		END


	 SELECT * FROM @SPERSON_RESULTS  


 END



